package com.cola.service;

import com.cola.dao.CommonDao;
import com.cola.dao.ShopDao;
import com.cola.entity.export.ExportShop;
import com.cola.enums.ImportType;
import com.cola.utils.DBControl;
import com.cola.utils.excel.ExcelUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 类名：ShopService
 * 功能：店铺管理业务逻辑类
 * 作者：Tony
 * 日期：2017/8/22.
 */
public class ShopService {
    private final Logger logger = LoggerFactory.getLogger(getClass());
    private static volatile ShopService instance = null;

    public static ShopService getInstance() {
        if (instance == null) {
            synchronized (ShopService.class) {
                if (instance == null) {
                    instance = new ShopService();
                }
            }
        }
        return instance;
    }

    /**
     * 获取店铺列表
     * @param filter 搜索条件
     * @return 更新过的列表数据
     * @author tony
     * @since 2017/9/7 11:46
     */
    public List<Map<String, Object>> getShopList(Integer beginNum, Integer pageSize, String filter) {
        List<Map<String,Object>> list = ShopDao.getInstance().getShopList(beginNum, pageSize, filter);
        //根据查询出的编号获得地区区域的名称并替换
        list = getAreaNameByAreaNo(list);
        //根据查询出的经销商数量确认是否有指定经销商
        list = checkSystemNo(list);
        return list;
    }

    /**
     * 获取地区区域名称
     * @param list 列表数据
     * @return list 更新过的列表数据
     * @author tony
     * @since 2017/9/7 10:11
     */
    public List<Map<String,Object>> getAreaNameByAreaNo(List<Map<String,Object>> list){
        String city = "";
        String area = "";
        String channel_type_no = "";
        String city_name = "";
        String area_name = "";
        String channel_type = "";
        //根据查询出的地址编号找到地址名称
        for(int i = 0;i<list.size();i++){
            city = (String) list.get(i).get("city");
            area = (String) list.get(i).get("area");
            channel_type_no = (String) list.get(i).get("channel_type_no");
            city_name = getAreaNameByAreaNo(city);
            area_name = getAreaNameByAreaNo(area);
            channel_type = getChannelType(channel_type_no);
            //替换列表里的地区和区域的字段
            list.get(i).put("city",city_name);
            list.get(i).put("area",area_name);
            list.get(i).put("channel_type_no",channel_type);
        }
        return list;
    }

    /**
     * 根据系统编号查询出经销商数量确认是否有指定经销商
     * @param list 获得的列表数据
     * @return list 更新过的列表数据
     * @author tony
     * @since 2017/9/7 11:36
     */
    public List<Map<String,Object>> checkSystemNo(List<Map<String,Object>> list){
        boolean flag = false;
        for(int i = 0;i<list.size();i++){
            flag = ShopDao.getInstance().checkSystemNo((String) list.get(i).get("customer_system_no"));
            if(flag){
                list.get(i).put("specify","是");
            }else {
                list.get(i).put("specify","否");
            }
        }
        return list;
    }

    /**
     * 查询店铺是否有指定经销商
     * @return false代表没有,true代表有
     * @author tony
     * @since 2017/9/6 16:30
     */
    public boolean checkMepNo(String customer_system_no){
        return ShopDao.getInstance().checkSystemNo(customer_system_no);
    }

    /**
     * 根据地区名称获取地区编号
     * @param area_name 地区名称
     * @return area_no 地区编号
     * @author tony
     * @since 2017/9/7 14:26
     */
    public String getAreaNoByAreaName(String area_name){

        return ShopDao.getInstance().getAreaNoByAreaName(area_name);
    }

    /**
     * 根据地区编号获取地区区域的名称
     * @param area_no 地区或区域的编号
     * @return 地区名称
     * @author tony
     * @since 2017/9/6 18:27
     */
    public String getAreaNameByAreaNo(String area_no){
        return ShopDao.getInstance().getAreaNameByAreaNo(area_no);
    }

    /**
     * 根据渠道类型编号获取渠道名称
     * @param channel_type_no 渠道类型编号
     * @return 渠道名称
     * @author tony
     * @since 2017/9/11 11:21
     */
    public String getChannelType(String channel_type_no){
        return ShopDao.getInstance().getChannelType(channel_type_no);
    }

    /**
     * 功能：获取店铺数量
     * 参数：筛选条件
     * 返回：符合条件的店铺数量
     */
    public Integer getShopCount(String filter) {
        return ShopDao.getInstance().getShopCount(filter);
    }

    /**
     * 功能：根据店铺编号获取店铺信息
     * 参数：店铺编号
     * 返回：对应的店铺信息
     */
    public Map<String, Object> getCustomerInfoById(Integer customerId,String customer_system_no) {
        List<String> list = ShopDao.getInstance().getMepNoByCustomerId(customer_system_no);
        Map<String,Object> map =  ShopDao.getInstance().getCustomerInfoById(customerId);
        map.put("specified_mep_no",list);
        return map;
    }

    /**
     * 功能：禁封店铺，解封店铺
     * 参数：店铺编号以及店铺在操作之后的状态
     * 返回：false为操作失败，true为操作成功
     */
    public boolean updateCheckStatus(Integer customerId, Integer checkStatus) {
        return ShopDao.getInstance().updateCheckStatus(customerId, checkStatus);
    }

    /**
     * 功能：管理店铺
     * 参数：被管理店铺的编号以及管理的信息
     * 返回：false为管理失败，true为管理成功
     */
    public boolean updateManagerCustomer(Integer customerId, String customer_no, String office_address, String routeId) {
        Integer check_status = (Integer) ShopDao.getInstance().getCustomerInfoById(customerId).get("check_status");

        return ShopDao.getInstance().updateManagerCustomer(customerId, customer_no, office_address, routeId, check_status);
    }

    /**
     * 功能：对导入店铺信息进行处理
     * 参数：excel文件路径
     * 返回：map
     * 作者：tony
     */
    public Map<String, Object> handleShopInfo(String fileUrl,String username,String filename,Long userId) {
        Map<String,Object> resultMap = new HashMap<String, Object>();
        resultMap.put("list","");
        resultMap.put("successCount",0);
        resultMap.put("failCount",0);
        Integer successCount = 0;
        Integer failCount = 0;
        Integer count = 0;
        Integer import_status = -1;
        List<String> list = new ArrayList<String>();//存放报错信息
        List<String> list1 ;
        List<String> list2 ;
        List<List<String>> importList = new ArrayList<List<String>>();//存放符合导入条件的数据
        try {
            List<List<String>> shopList = ExcelUtils.readExcel(fileUrl);
            count = shopList.size();
            if (shopList!=null) {
                //遍历shoplist,将每一条数据都与除本身所有的数据进行比较
                for (int i = 0;i<shopList.size();i++) {
                    list1 = shopList.get(i);
                    for (int j = 0;j<shopList.size();j++) {
                        list2 = shopList.get(j);
                        //最后一条数据
                        if(j!=shopList.size()-1) {
                            //数据有误或数据重复
                            if (compare(list1, list2).get("result").equals("error")||compare(list1,list2).get("result").equals("duplicate")) {
                                if (j!=i) {
//                                    failCount++;
                                    list.add(String.valueOf(compare(list1,list2).get("error")));//存入报错店铺的系统编号
                                    break;//跳出里循环
                                }
                            }else{
                                continue;//跳出当前循环
                            }
                        }else{
                            if (compare(list1, list2).get("result").equals("error")||compare(list1,list2).get("result").equals("duplicate")) {
                                if (j!=i) {
//                                    failCount++;
                                    list.add(String.valueOf(compare(list1,list2).get("error")));//存入报错店铺的系统编号
                                    break;//跳出里循环
                                }else{
                                    //shopList最后一组数据
                                    importList.add(list1);
                                }
                            }else{
                                importList.add(list1);
                            }
                        }
                    }
                }
                Map<String,Object> map = importShopInfo(importList);
                import_status = 1;
                successCount = (Integer) map.get("successCount");
                failCount = count-successCount;
                resultMap.put("result", list);
                resultMap.put("result2",map.get("error"));
                resultMap.put("successCount", successCount);
                resultMap.put("failCount", failCount);
            }
        } catch (Exception e) {
            logger.error("excel文件处理异常！",e);
        } finally {
            CommonDao.getInstance().addImportLog(userId, ImportType.SHOP_IMPORT.getDesc(),filename,fileUrl,username,
                    count,successCount,failCount,import_status,"");
        }
        return resultMap;
    }



    /**
     * 功能：删除表内原有的和导入有冲突的数据并导入数据
     * 参数：符合条件的导入数据
     * 返回：插入成功和插入失败的条数
     * 作者：tony
     */
    public Map<String, Object> importShopInfo(List<List<String>> importList){
        Map<String,Object> map = new HashMap<String, Object>();
        Map<String,Object> map1 = new HashMap<String, Object>();
        Integer successCount=0;
        Integer failCount = 0;
        Connection conn = null;
        //查找和导入数据系统编号一样的数据进行删除
        try {
            conn = DBControl.getConnection();
            conn.setAutoCommit(false);
            for(int i = 0;i<importList.size();i++){
                //进行删除
                ShopDao.getInstance().deleteInfo(conn,importList.get(i).get(0));
            }
            //逐条导入数据到推荐经销商表
            for(int j=0;j<importList.size();j++){
                boolean flag = ShopDao.getInstance().importShopInfo(conn,importList.get(j));
                if(flag){
                    successCount++;
                }else{
                    failCount++;
                }
            }
            //导入申美，办事处，路线号等到店铺表
            map1 = importCustomerInfo(conn,importList);
            conn.commit();
            map.put("error",map1.get("error"));
            map.put("successCount",successCount);
            map.put("failCount",failCount);
        } catch (Exception e) {
            if(conn!=null){
                try {
                    conn.rollback();
                } catch (Exception w) {
                    logger.error("导入数据回滚异常！",w);
                }
            }
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException se) {
                se.printStackTrace();
            }
            DbUtils.closeQuietly(conn);
        }
        return map;
    }

    /**
     * 导入申美，办事处，路线号等到店铺表
     * @param conn 数据库连接
     * @param list 导入的list
     * @return map1 报错信息
     * @author tony
     * @since 2017/9/11 12:56
     */
    public Map<String,Object> importCustomerInfo(Connection conn, List<List<String>> list) throws Exception{
        //返回错误信息map，如果没有就返回""
        Map<String,Object> map1 = new HashMap<>();
        map1.put("error","");
        List<String> list1 = new ArrayList<>();
        try {
            for(int k =0;k<list.size();k++){
                Map<String,Object> map;
                map = getShopInfoByList(list.get(k));
                String result = (String) ShopDao.getInstance().updateCustomerInfo(conn,map).get("result");
                String error = (String) ShopDao.getInstance().updateCustomerInfo(conn,map).get("error");
                if (result.equals("fail")) {
                    list1.add(error);
                }
            }
            map1.put("error",list1);
        } catch (Exception e) {
            logger.error("导入异常！",e);
            throw e;
        }
        return map1;
    }

    /**
     * 确认是否导入店铺信息(遍历导入店铺信息进行比较)
     * @return map
     * @author tony
     * @since 2017/9/6 13:59
     */
    public Map<String,Object> compare(List<String> list1, List<String> list2){
        //比较店铺信息
        Map<String,Object> map = new HashMap<String,Object>();
        Map<String,Object> map1 = getShopInfoByList(list1);
        Map<String,Object> map2 = getShopInfoByList(list2);
        map.put("result","error");
        if(map1.get("customer_system_no").equals(map2.get("customer_system_no"))){
            if(map1.get("customer_no").equals(map2.get("customer_no"))){
                if(map1.get("office_address").equals(map2.get("office_address"))){
                    if(map1.get("route_id").equals(map2.get("route_id"))){
                        if (map1.get("customer_sub_channel_no").equals(map2.get("customer_sub_channel_no"))) {
                            if (map1.get("consumer_sub_channel_no").equals(map2.get("consumer_sub_channel_no"))) {
                                //重复的数据
                                if(map1.get("mep_no").equals(map2.get("mep_no"))){
                                    map.put("error","系统编号为"+map1.get("customer_system_no")+"的数据重复!");
                                    map.put("result","duplicate");
                                }else{
                                    map.put("result","success");
                                }
                            }else{
                                //消费者子渠道不同
                                map.put("error","系统编号为"+map1.get("customer_system_no")+"的数据出错!");
                            }
                        }else{
                            //客户子渠道不同
                            map.put("error","系统编号为"+map1.get("customer_system_no")+"的数据出错!");
                        }
                    }else{
                        //线路号不同
                        map.put("error","系统编号为"+map1.get("customer_system_no")+"的数据出错!");
                    }
                }else{
                    //办事处不同
                    map.put("error","系统编号为"+map1.get("customer_system_no")+"的数据出错!");
                }
            }else{
                //申美编号不同
                map.put("error","系统编号为"+map1.get("customer_system_no")+"的数据出错!");
            }
        }else{
            map.put("result","success");
        }
        return map;
    }

    /**
     * 根据list获取要导入店铺消息
     * @return map
     * @author tony
     * @since 2017/9/6 13:58
     */
    public Map<String, Object> getShopInfoByList(List<String> list) {
        try {
            Map<String, Object> shopInfo = new HashMap<String, Object>();
            shopInfo.put("customer_system_no",list.get(0));
            shopInfo.put("customer_no",list.get(10));
            shopInfo.put("office_address",list.get(11));
            shopInfo.put("route_id",list.get(12));
            shopInfo.put("mep_no",list.get(13));
            shopInfo.put("customer_sub_channel_no",list.get(14));
            shopInfo.put("consumer_sub_channel_no",list.get(15));
            return shopInfo;
        } catch (Exception e) {
            return null;
        }
    }
    
    /**
     * 获取导出的店铺列表
     * @return List<ExportShop> 店铺列表
     * @author tony
     * @since 2017/9/6 15:22
     */
    public List<ExportShop> getExportShopList(String filter){
        return ShopDao.getInstance().getExportShopList(filter);
    }

    /**
     * 获得导出店铺列表的过滤条件
     * @return filters 过滤条件
     * @author tony
     * @since 2017/9/7 16:26
     */
    public String getFilters(HttpServletRequest request){
        StringBuffer filters = new StringBuffer();
        String city = request.getParameter("city");
        String area = request.getParameter("area");
        String shop_mobile = request.getParameter("shop_mobile");
        String customer_system_no = request.getParameter("customer_system_no");
        String channel_type = request.getParameter("channel_type");
        Integer check_status = Integer.valueOf( request.getParameter("check_status"));
        String route_id = request.getParameter("route_id");
        String office_address = request.getParameter("office_address");
        if(StringUtils.isNotEmpty(city)){
            filters.append(" and city='").append(city).append("'");
        }
        if(StringUtils.isNotEmpty(area)){
            String area_no = ShopService.getInstance().getAreaNoByAreaName(area);
            filters.append(" and area='").append(area_no).append("'");
        }
        if(StringUtils.isNotEmpty(shop_mobile)){
            filters.append(" and customer_mobile'").append(shop_mobile).append("'");
        }
        if(StringUtils.isNotEmpty(customer_system_no)){
            filters.append(" and customer_system_no'").append(customer_system_no).append("'");
        }
        if(StringUtils.isNotEmpty(channel_type)){
            filters.append(" and channel_type='").append(channel_type).append("'");
        }
        if(check_status>-3){
            filters.append(" and check_status='").append(check_status).append("'");
        }
        if(StringUtils.isNotEmpty(route_id)){
            filters.append(" and route_id='").append(check_status).append("'");
        }
        if(StringUtils.isNotEmpty(office_address)){
            filters.append(" and office_address='").append(office_address).append("'");
        }
        request.setAttribute("city",city);
        request.setAttribute("area",area);
        request.setAttribute("shop_mobile",shop_mobile);
        request.setAttribute("customer_system_no",customer_system_no);
        request.setAttribute("channel_type",channel_type);
        request.setAttribute("check_status",check_status);
        request.setAttribute("route_id",route_id);
        request.setAttribute("office_address",office_address);
        return filters.toString();


    }

    public static void main(String[] args){
//        System.out.println(getInstance().handleShopInfo("http://cocacola-sh.oss-cn-hangzhou.aliyuncs.com/h5_upload/images/8H4aicrPab.xlsx"),);
    }
}
